package top.stmo;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class JDBCTest3 {

    public static void main(String[] args) throws Exception {

        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=" +
                "UTF8&autoReconnect=true&useSSL=false";

        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //获取执行sql的对象
        Statement stmt = conn.createStatement();

        //定义sql语句
        String sql = "select id,name,phone from tb_user where id < 5";

        //执行sql
        ResultSet resultSet = stmt.executeQuery(sql);

        ArrayList<Person> personArrayList = new ArrayList<>();

        //遍历ResultSet中的数据
        while (resultSet.next()) {
            //获取数据
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            long phone = resultSet.getLong(3);

            Person person = new Person();
            person.setId(id);
            person.setName(name);
            person.setPhone(phone);
            personArrayList.add(person);
        }

        for (Person p : personArrayList) {
            System.out.print(p.getId());
            System.out.print(p.getName());
            System.out.println(p.getPhone());
        }

        //释放资源
        stmt.close();
        conn.close();

    }


    /**
     *  ---------------------SQL注入问题--------------------------
     *
     *      PreparedStatement
     *
     *      预编译SQL，性能更高
     *      防止SQL注入
     *
     *      executeUpdate(); 执行DDL语句和DML语句
     *      executeQuery(); 执行DQL语句
     *      调用这两个方法时不需要传递SQL语句，因为获取SQL语句执行对象时已经对SQL语句进行预编译了。
     *
     */
    @Test
    public void test1() throws Exception {

        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=" +
                "UTF8&autoReconnect=true&useSSL=false";

        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收用户输入 用户名和密码
        String name = "lisi";
        String pwd = "222";

        String sql = "select * from login where username = ? and password = ? ";

        // 获取stmt对象
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1,name);
        statement.setString(2,pwd);

        // 执行sql
        ResultSet resultSet = statement.executeQuery();
        // 判断登录是否成功
        if(resultSet.next()){
            System.out.println("登录成功~");
        }else{
            System.out.println("登录失败~");
        }
        //7. 释放资源
        resultSet.close();
        statement.close();
        conn.close();
    }

}
